package mavenWebProject;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

public interface productsMapper {
	
	/*
	 * 获取所有商品
	 */
	public List<products> selectAll();
	
	/*
	 * 根据名称获取指定商品
	 */
	public List<products> selectByName(@Param("name")String name);
	/*
	 * 根据id获取指定商品
	 */
	@Select("SELECT * FROM tb_products WHERE id=#{id}")
	public List<products> selectById(@Param("id")int id);
	/*
	 * 按照销量顺序获取商品数据
	 */
	@Select("SELECT * FROM tb_products WHERE sold>0 ORDER BY sold DESC")
	public List<products> selectOrderedBySold();
	
	
	
	/*
	 * 查询购物车中所有数据
	 */
	@Select("SELECT * FROM tb_car")
	public List<car> selectAllCar();
	/*
	 * 根据用户名查询购物车中所有数据
	 */
	@Select("SELECT * FROM tb_car WHERE username=#{username}")
	public List<car> selectAllCarByUsername(@Param("username")String username);
	/*
	 * 插入购物车
	 */
	public void insertIntoCar(@Param("name")String name,@Param("price")int price,@Param("amount")int amount,@Param("size")String size,@Param("img")String img,@Param("username")String username);
	/*
	 * 根据商品名称和尺码获取购物车数据
	 */
	@Select("SELECT * FROM tb_car WHERE name=#{name} AND size=#{size}")
	public List<car> selectCarByNameSize(@Param("name")String name,@Param("size")String size);
	/*
	 * 根据id查询购物车数据
	 */
	@Select("SELECT * FROM tb_car WHERE id=#{id}")
	public List<car> selectCarById(@Param("id")int id);
	/*
	 * 添加相同购物车商品更新数量
	 */
	@Update("UPDATE tb_car set amount=#{amount} WHERE name=#{name} AND size=#{size}")
	public void updateCarGoodAmount(@Param("amount")int amount,@Param("name")String name,@Param("size")String size,@Param("username")String username);
	/*
	 * 根据用户名、id删除
	 */
	@Delete("DELETE FROM tb_car WHERE username=#{username} AND id=#{id}")
	public void deleteFromCar(@Param("username")String username,@Param("id")int id);
	
	
	
	
	/*
	 * 根据用户名查询订单表中所有数据
	 */
	@Select("SELECT * FROM tb_order WHERE username=#{username}")
	public List<order> selectAllOrder(@Param("username")String username);
	/*
	 * 用户修改用户名后修改订单表中的姓名
	 */
	@Update("UPDATE tb_order SET username=#{username} WHERE username=#{targetUsername}")
	public void updateOrderUsername(@Param("username")String username,@Param("targetUsername")String targetUsername);
	/*
	 * 用户修改用户名后修改购物车中的姓名
	 */
	@Update("UPDATE tb_car SET username=#{username} WHERE username=#{targetUsername}")
	public void updateCarUsername(@Param("username")String username,@Param("targetUsername")String targetUsername);
	/*
	 * 查询订单表所有数据
	 */
	@Select("SELECT * FROM tb_order ORDER BY stage")
	public List<order> selectAllOrders();
	/*
	 * 查询订单表中不重复的用户名
	 */
	@Select("SELECT DISTINCT username FROM tb_order")
	public List<order> selectDistinctOrder();
	/*
	 * 根据id查询订单表中所有数据
	 */
	@Select("SELECT * FROM tb_order WHERE id=#{id}")
	public List<order> selectAllOrderById(@Param("id")int id);
	/*
	 * 根据用户名、商品名、尺码、订单开启时间、订单状态查询订单表中
	 */
	@Select("SELECT * FROM tb_order WHERE username=#{username} AND name=#{name} AND size=#{size} AND opentime=#{opentime} AND stage=#{stage}")
	public List<order> selectOrderSame(@Param("username")String username,@Param("name")String name,@Param("size")String size,@Param("opentime")String opentime,@Param("stage")String stage);
	/*
	 * 添加对应商品进入订单表，执行扣费、减少库存等操作；
	 */
	@Insert("INSERT into tb_order(username,img,name,price,amount,size,stage,opentime,closetime) VALUES(#{username},#{img},#{name},#{price},#{amount},#{size},#{stage},#{opentime},#{closetime})")
	public void insertIntoOrder(@Param("username")String username,@Param("img")String img,@Param("name")String name,
			@Param("price")int price,@Param("amount")int amount,@Param("size")String size,@Param("stage")String stage,@Param("opentime")String opentime,@Param("closetime")String closetime);
	/*
	 * 更新订单中的数量；
	 */
	@Update("UPDATE tb_order SET amount+=#{amount} WHERE username=#{username} AND name=#{name} AND size=#{size} AND opentime=#{opentime} AND stage=#{stage}")
	public void updateOrderAmount(@Param("amount")int amount,@Param("username")String username,@Param("name")String name,@Param("size")String size,@Param("opentime")String opentime,@Param("stage")String stage);
	/*
	* 更新用户表中用户资金，即扣费;或者给系统账户加钱
	*/
	@Update("UPDATE tb_user set money-=#{money} WHERE username=#{username}")
	public void updateChangeMoney(@Param("money")int money,@Param("username")String username);
	/*
	 * 更新商品表中的库存数据
	 */
	@Update("UPDATE tb_products set storage-=#{amount},sold+=#{amount} WHERE name=#{name}")
	public void updateChangeStorage(@Param("amount")int amount,@Param("name")String name);
	/*
	 * 确认收到货物
	 */
	@Update("UPDATE tb_order set stage=#{confirm},closetime=#{closetime} WHERE id=#{id}")
	public void updateOrderConfirm(@Param("confirm")String confirm,@Param("closetime")String closetime,@Param("id")int id);
	/*
	 * 删除订单表中某条数据
	 */
	@Delete("DELETE FROM tb_order WHERE id=#{orderId}")
	public void deleteOrderById(@Param("orderId")int orderId);
	/*
	 * 退回资金
	 */
	@Update("UPDATE tb_user SET money+=#{money} WHERE username=#{username}")
	public void updateReturnMoney(@Param("money")int money,@Param("username")String username);
	/*
	 * 退回库存
	 */
	@Update("UPDATE tb_products SET storage+=#{num},sold-=#{num} WHERE name=#{name}")
	public void updateReturnStorage(@Param("num")int num,@Param("name")String name);
	
	/*
	 * 用户注销账户后删除该用户在购物车中加购的商品
	 */
	@Delete("DELETE FROM tb_car WHERE username=#{username}")
	public void deleteCarByUsername(@Param("username")String username);
	
	
	/*
	 * 管理员根据订单状态查询订单
	 */
	@Select("SELECT * FROM tb_order WHERE stage=#{stage}")
	public List<order> selectOrderByStage(@Param("stage")String stage);
	/*
	 * 管理员根据id修改订单上架状态
	 */
	@Update("UPDATE tb_products SET status=#{status} WHERE id=#{id}")
	public void updateProductsStatus(@Param("status")int status,@Param("id")int id);
	/*
	 * 管理员根据id删除商品
	 */
	@Delete("DELETE FROM tb_products WHERE id=#{id}")
	public void deleteProducts(@Param("id")int id);
	/*
	 * 管理员根据id修改订单状态
	 */
	@Update("UPDATE tb_order SET stage=#{stage} WHERE id=#{id}")
	public void updateOrderStage1(@Param("id")int id,@Param("stage")String stage);
	/*
	 * 管理员根据id修改订单状态同时关闭订单
	 */
	@Update("UPDATE tb_order SET stage=#{stage},closetime=#{closetime} WHERE id=#{id}")
	public void updateOrderStage2(@Param("id")int id,@Param("stage")String stage,@Param("closetime")String closetime);
	/*
	 * 管理员根据商品名称更新订单状态位‘待发货’的订单状态为‘退回’。因为管理员下架了商品所以相应商品没发货的订单也要退回,商品删除同理。
	 */
	@Update("UPDATE tb_order SET stage=#{stage},closetime=#{closetime} WHERE name=#{name}")
	public void updateOrderStage(@Param("name")String name,@Param("stage")String stage,@Param("closetime")String closetime);
	/*
	 * 管理员根据id修改商品信息
	 */
	@Update("UPDATE tb_products SET name=#{name},price=#{price},storage=#{storage},all_ordered=#{all_ordered},for_group=#{for_group},kind=#{kind} WHERE id=#{id}")
	public void updateProducts(@Param("id")int id,@Param("name")String name,@Param("price")int price,@Param("storage")int storage,@Param("all_ordered")int all_ordered,@Param("for_group")String for_group,@Param("kind")String kind);
	/*
	 * 管理员添加商品
	 */
	@Insert("INSERT INTO tb_products(name,img,price,storage,sold,status,all_ordered,for_group,kind) VALUES(#{addname},#{addimg},#{addprice},#{addstorage},#{addsold},#{addstatus},#{addall_ordered},#{addfor_group},#{addkind})")
	public void insertProducts(@Param("addname")String addname,@Param("addimg")String addimg,@Param("addprice")int addprice,@Param("addstorage")int addstorage,@Param("addsold")int addsold,@Param("addstatus")int addstatus,@Param("addall_ordered")int addall_ordered,@Param("addfor_group")String addfor_group,@Param("addkind")String addkind);
}